package com.gym.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import com.gym.model.entity.Course;
import com.gym.model.entity.Payment;
import com.gym.model.entity.User;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

public class DAOPayment extends DAO {

	public DAOPayment() throws ConnessioneException {
		super();
	}


	public int insert(Payment p) throws ViolazioneIntegritaException, MetodoDaoException{

		String query="INSERT INTO payment(iduser, idcourse, months, amount) "
				+"values			(?, 		?, 		?, 			? )";

		try{

			pst = con.prepareStatement(query); //, Statement.RETURN_GENERATED_KEYS
			pst.setInt(1, p.getIduser());
			pst.setInt(2, p.getIdcourse());
			pst.setShort(3, p.getMonths());
			pst.setDouble(4, p.getAmount());

			//esegue la query
			return pst.executeUpdate();

		} catch(MySQLIntegrityConstraintViolationException e){

			if(e.getErrorCode()==1452)
				throw new ViolazioneIntegritaException("ERRORE INSERT PAYMENT: FK fuori dominio "+e.getMessage());
			else
				throw new ViolazioneIntegritaException("ERRORE INSERT PAYMENT: altra violazione R.I. "+e.getMessage()+
						"\nErrorCode="+e.getErrorCode());
			
		} catch(SQLException e){
			throw new MetodoDaoException("ERRORE INSERT PAYMENT "+e.getMessage()+
					"\nErrorCode="+e.getErrorCode()+"\nQUERY: "+pst);
		}finally{
			try {pst.close();} catch(SQLException e){}
		}
	}



	public ArrayList<Payment> select() throws DatoNonTrovatoException, MetodoDaoException {

		ArrayList<Payment> al = new ArrayList<>();
		try {
			stm = con.createStatement(); 
			res = stm.executeQuery("SELECT *"
								  +" FROM payment "
								  +" ORDER BY iduser");

			while(res.next()) { //cicla il ResultSet e costruisce oggetto Payment
				al.add( componiPayment() ); //aggiungo l'oggetto Payment in lista
			}
			if(al.size()==0)
				throw new DatoNonTrovatoException("Attenzione tabella pagamenti vuota!");
			else
				return al;

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT PAYMENT: "+e.getMessage()
					+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {stm.close();} catch(SQLException e) {}
		}
	}


	/**
	 * metodo per estrarre il Course corrispondente alla PK passata in argomento
	 * @param id valore intero della PK
	 * @return Course bean della tabella COURSE
	 * @throws MetodoDaoException
	 * @throws DatoNonTrovatoException
	 */
	public Payment select(int id) throws MetodoDaoException, DatoNonTrovatoException {

		try {
			pst = con.prepareStatement
							("SELECT *"
							+"FROM payment "
							+"WHERE iduser = ? ");

			pst.setInt(1, id);        //sostituisce i marcatori ?
			res = pst.executeQuery(); //esegue la query!

			if(res.next()) 
				return componiPayment(); 
			else
				throw new DatoNonTrovatoException
				("Nessuna tupla trovata in PAYMENT per PK id="+id);

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT PAYMENT con argomento id: "+e.getMessage()+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}
	
	public boolean search (int iduser, int idcourse) throws MetodoDaoException, DatoNonTrovatoException {

		try {
			pst = con.prepareStatement
							("SELECT *"
							+"FROM payment "
							+"WHERE iduser = ? AND idcourse = ? ");

			pst.setInt(1, iduser);
			pst.setInt(2, idcourse);
			res = pst.executeQuery();

			if(res.next()) 
				return true;
			else
				return false;

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT PAYMENT con argomento id: "+e.getMessage()+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}

	public Payment delete(int id) throws MetodoDaoException, DatoNonTrovatoException {
		try {
			Payment payment = select(id);

			pst = con.prepareStatement("DELETE FROM payment WHERE iduser = ?");
			pst.setInt(1, id); 
			pst.executeUpdate(); //esegue la query di DELETE

			return payment;

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE DELETE PAYMENT con argomento id: "+e.getMessage()
					+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}	
	
	public void delete(int iduser, int idcourse) throws MetodoDaoException, DatoNonTrovatoException {
		
		try {
			pst = con.prepareStatement("DELETE FROM payment WHERE iduser = ? AND idcourse = ?");
			pst.setInt(1, iduser);
			pst.setInt(2, idcourse);
			pst.executeUpdate(); //esegue la query di DELETE

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE DELETE PAYMENT con argomento id: "+e.getMessage()
					+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}	


	//METODI DI SERVIZIO: tutti private!

	private Payment componiPayment() throws SQLException{
		return new Payment(res.getInt(1), res.getInt(2), res.getShort(3), res.getDouble(4), res.getDate(5));		
	}


	//METODO main() per TESTARE il DAO

	public static void main(String[] args) throws ViolazioneIntegritaException {

		Payment pnew = new Payment(4, 2, (short)11, 20);

		try {
			DAOPayment dao = new DAOPayment();

			//eseguire i metodi in 'logica' cascata:

			dao.insert(pnew);
			
			System.out.println("\nselect()........: ");
			for(Payment p : dao.select()) System.out.println(p);

			dao.delete(4);

		} catch (ConnessioneException | MetodoDaoException | DatoNonTrovatoException e) {
			e.printStackTrace();
		}


	}
	
	public Payment select( User user, Course course ) throws DatoNonTrovatoException, MetodoDaoException {
		try {
			pst = con.prepareStatement
							("SELECT *"
							+"FROM payment "
							+"WHERE iduser = ? and idcourse = ? order by pdate desc ");

			pst.setInt(1, user.getId());
			pst.setInt(2, course.getId());
			res = pst.executeQuery(); //esegue la query!

			if(res.next()) 
				return componiPayment(); 
			return null;

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT PAYMENT con argomento id: "+e.getMessage()+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}
}
